﻿IF object_id(N'[AMAP].[dbo].[sp_ReadRequestId]') is not null
	DROP PROCEDURE sp_ReadRequestId
GO
CREATE PROCEDURE sp_ReadRequestId
	@RequestId int
AS
BEGIN
	SELECT 
		o.Id as OfferId,
		p.Name as Product,
		o.Price as Price,
		o.Unit as Unit,
		WantedQuantity as Wanted,
		ProvidedQuantity as Provided
	FROM
		AMAP.dbo.RequestSet as r 
		inner join AMAP.dbo.OfferSet as o 
			on r.OfferId = o.Id
		inner join AMAP.dbo.ProductSet as p
			on o.ProductId = p.Id
	WHERE 
		r.AmapId = (
			SELECT TOP 1 Id 
				FROM AMAP.dbo.AmapSet 
				WHERE SQLServerLoginName = (SELECT TOP 1 ORIGINAL_LOGIN())
		)
		AND r.Id = @RequestId
END
GO


IF object_id(N'[AMAP].[dbo].[sp_RequestProduct]') is not null
	DROP PROCEDURE sp_RequestProduct
GO
CREATE PROCEDURE sp_RequestProduct
	@OfferId int,
	@Quantity int
AS
BEGIN
	INSERT INTO AMAP.dbo.RequestSet(WantedQuantity, ProvidedQuantity, AmapId, OfferId)
		VALUES (
			@Quantity,
			-1,
			(SELECT TOP 1 Id from AMAP.dbo.AmapSet WHERE SQLServerLoginName = (SELECT TOP 1 ORIGINAL_LOGIN())),
			@OfferId
		)

	SELECT SCOPE_IDENTITY() AS RequestId
END
GO


IF object_id(N'[AMAP].[dbo].[Offers]') is not null
	DROP VIEW Offers
GO
CREATE VIEW Offers AS
	SELECT
		o.Id as OfferId,
		p.Name as Product,
		o.Price as Price,
		o.Unit as Unit,
		o.Quantity as Quantity
	FROM
		AMAP.dbo.OfferSet as o
		inner join AMAP.dbo.ProductSet as p on o.ProductId = p.Id
	WHERE o.CreationDate > GETDATE() - 7
GO